﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
using System.Windows.Forms;

namespace pryConvertTXT.Clases_Util
{
    public class cnDB
    {

        Parametros cnStr = new Parametros();
        public SqlConnection cn = new SqlConnection();

        // para conectar a las base de datos
        public void ConexBD(System.Data.SqlClient.SqlConnection cn)
        {
            try
            {
                // cn.ConnectionString = cnStr.Parametro("cnsql") ' cadena de coenxion/puede reemplazar por un codigo que genere una cadena de conexion
                if (cn.State == ConnectionState.Open)
                {
                    cn.Close();
                }
                cn.ConnectionString = ConfigurationManager.ConnectionStrings["ConexionData"].ConnectionString;
            }
            catch (Exception ex)
            {
                throw ex;
                //Interaction.MsgBox(ex.Message, MsgBoxStyle.Critical);
            }

        }
        public void CargarComboxGrid(string tabla, DataGridViewComboBoxColumn colcombo)
        {
            try
            {
                ConexBD(cn);
                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter("select * from " + tabla, cn);
                DataSet ds = new DataSet();
                int i = 0;
                da.Fill(ds);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    while (i <= ds.Tables[0].Rows.Count - 1)
                    {
                        colcombo.Items.Add(ds.Tables[0].Rows[i][0] + ": " + ds.Tables[0].Rows[i][1]);
                        i = i + 1;
                    }
                }
            }
            catch (Exception ex)
            {
                //Interaction.MsgBox(ex.Message, MsgBoxStyle.Critical);
            }
        }

        //Function BuscaCrea(ByVal xSql As String) As String
        //    Dim Resul As String = ""
        //    Try
        //        Dim frm As New frmBuscaCrea
        //        frm.sql = xSql
        //        frm.sqlCad = frm.sql
        //        frm.ShowDialog()
        //        Resul = frm.sqlResult
        //        frm.Close()
        //    Catch ex As Exception
        //        MsgBox(ex.Message, MsgBoxStyle.Critical)
        //    End Try
        //    Return Resul
        //End Function
        public string MuestraDescripcion(string xsql)
        {
            ConexBD(cn);
            string resul = "";
            try
            {
                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(xsql, cn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                if (ds.Tables[0].Rows.Count != 0)
                {
                    resul = ds.Tables[0].Rows[0][0] + ": " + ds.Tables[0].Rows[0][1];
                }
                else
                {
                    resul = "";
                }
                da.Dispose();
                ds.Dispose();
            }
            catch (Exception ex)
            {
                //Interaction.MsgBox(ex.Message, MsgBoxStyle.Critical);
            }
            return resul;
        }

        public void InsertarRegistros(string NomTabla, string NomCampos, string Valores)
        {
            // genera un insert basico
            try
            {
                ConexBD(cn);
                System.Data.SqlClient.SqlCommand comInsertar = new System.Data.SqlClient.SqlCommand();
                comInsertar.Connection = cn;
                comInsertar.CommandText = "insert into " + NomTabla + " ( " + NomCampos + ")" + " values (" + Valores + ")";
                if (cn.State == ConnectionState.Open)
                {
                    cn.Close();
                }
                cn.Open();
                comInsertar.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Hand);
            }

        }

        public void EliminarRegistros(string Tabla, string Filtro)
        {
            try
            {
                ConexBD(cn);
                System.Data.SqlClient.SqlCommand comEliminar = new System.Data.SqlClient.SqlCommand();
                comEliminar.Connection = cn;
                comEliminar.CommandText = "delete from " + Tabla + " where " + Filtro;
                if (cn.State == ConnectionState.Open)
                {
                    cn.Close();
                }
                cn.Open();
                comEliminar.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Hand);
            }
        }
        public void MostrarRegDataGrid(string SqlQuery, DataGridView DataGrid)
        {
            ConexBD(cn);
            //cn.Open()
            // muestra contenido de una consulta en un grid
            try
            {
                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(SqlQuery, cn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                var _with1 = DataGrid;
                DataGrid.DataSource = ds.Tables[0];
                DataGrid.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                DataGrid.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                ds.Dispose();
                da.Dispose();
            }
            catch (Exception EX)
            {
                //Interaction.MsgBox(EX.Message, MsgBoxStyle.Critical);
            }

        }
        public void BuscarRegDataGrid(string SqlQuery, string filtro, DataGridView DataGrid)
        {
            ConexBD(cn);
            //busca y mustra un resultado de una consulta o tabla
            try
            {
                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(SqlQuery + " " + filtro, cn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                DataGrid.DataSource = ds.Tables[0];
                DataGrid.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                DataGrid.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                ds.Dispose();
                da.Dispose();
            }
            catch (Exception EX)
            {
                //Interaction.MsgBox(EX.Message, MsgBoxStyle.Critical);
            }
        }
        public void CargarCombos(string tabla, ComboBox cbo)
        {
            try
            {
                // cargar datos en un combo
                ConexBD(cn);
                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter("select * from " + tabla, cn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                if (ds.Tables[0].Rows.Count != 0)
                {
                    int i = 0;
                    cbo.Items.Clear();
                    while (i <= ds.Tables[0].Rows.Count - 1)
                    {
                        cbo.Items.Add(ds.Tables[0].Rows[i][0] + " : " + ds.Tables[0].Rows[i][1]);
                        i += 1;
                    }
                }
            }
            catch (Exception ex)
            {
                //Interaction.MsgBox(ex.Message, MsgBoxStyle.Critical);
            }
        }
        public string TraerCodigo(Control ControlText)
        {
            string resul = "";
            // trae el dato de una tabla (la primary key)
            try
            {
                if (ControlText.Text.Length != 0)
                {
                    String[] pos = ControlText.Text.Split(':');
                    if (pos.Length >= 1)
                    {
                        resul = pos[0];
                    }
                    else
                    {
                        resul = "";
                    }

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return resul;
        }
        public string UnDato(string xSql)
        {
            ConexBD(cn);
            string resul = "";
            try
            {
                System.Data.SqlClient.SqlCommand sqlComan = new System.Data.SqlClient.SqlCommand(xSql, cn);
                if (cn.State == ConnectionState.Open)
                {
                    cn.Close();
                }
                cn.Open();
                resul = ((sqlComan.ExecuteScalar()) == null ? "" : sqlComan.ExecuteScalar()).ToString();
                cn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
                //Interaction.MsgBox(ex.Message, MsgBoxStyle.Critical);
            }
            return resul;
        }
        public string NombreRPT(string rpt)
        {
            string resul = "";
            try
            {
                System.Data.SqlClient.SqlCommand sqlcoman = new System.Data.SqlClient.SqlCommand("select NO_ARCHIVO FROM NOMBRE_REPORTEs WHERE co_reporte='" + rpt + "'", cn);
                if (cn.State == ConnectionState.Open)
                {
                    cn.Close();
                }
                cn.Open();
                resul = (sqlcoman.ExecuteScalar() == null ? "" : sqlcoman.ExecuteScalar()).ToString();
                if (string.IsNullOrEmpty(resul))
                {
                    MessageBox.Show("No hay Reporte de Boletas Configurado");
                }
                cn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
                //Interaction.MsgBox(ex.Message, MsgBoxStyle.Critical);
            }
            return resul;
        }
        public int Totalregistros(string sqlCad)
        {
            ConexBD(cn);
            int resul = 0;
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sqlCad, cn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                resul = ds.Tables[0].Rows.Count;
                ds.Dispose();
                da.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
                //Interaction.MsgBox(ex.Message, MsgBoxStyle.Critical);
            }
            return resul;
        }
        public string Letras(string numero)
        {
            string functionReturnValue = null;
            try
            {
                //********Declara variables de tipo cadena************
                string palabras = "";
                string entero = "";
                string dec = "";
                string flag = "";

                //********Declara variables de tipo entero***********
                int num = 0;
                int x = 0;
                int y = 0;

                flag = "N";

                //**********Número Negativo***********
                if (Mid(numero, 1, 1) == "-")
                {
                    numero = Mid(numero, 2, numero.ToString().Length - 1).ToString();
                    palabras = "menos ";
                }

                //**********Si tiene ceros a la izquierda*************
                for (x = 1; x <= numero.ToString().Length; x++)
                {
                    if (Mid(numero, 1, 1) == "0")
                    {
                        numero = (Mid(numero, 2, numero.ToString().Length).ToString()).ToString().Trim();
                        if (numero.ToString().Trim().Length == 0)
                            palabras = "";
                    }
                    else
                    {
                        break; // TODO: might not be correct. Was : Exit For
                    }
                }

                //*********Dividir parte entera y decimal************
                for (y = 1; y <= numero.Length; y++)
                {
                    if (Mid(numero, y, 1) == ".")
                    {
                        flag = "S";
                    }
                    else
                    {
                        if (flag == "N")
                        {
                            entero = entero + Mid(numero, y, 1);
                        }
                        else
                        {
                            dec = dec + Mid(numero, y, 1);
                        }
                    }
                }

                if (dec.Length == 1)
                    dec = dec + "0";

                //**********proceso de conversión***********
                flag = "N";

                if (Convert.ToInt64 (numero) <= 999999999)
                {
                    for (y = entero.Length; y >= 1; y += -1)
                    {
                        num = entero.Length - (y - 1);
                        switch (y)
                        {
                            case 3:
                            case 6:
                            case 9:
                                //**********Asigna las palabras para las centenas***********
                                switch (Mid(entero, num, 1))
                                {
                                    case "1":
                                        if (Mid(entero, num + 1, 1) == "0" && Mid(entero, num + 2, 1) == "0")
                                        {
                                            palabras = palabras + "Cien ";
                                        }
                                        else
                                        {
                                            palabras = palabras + "Ciento ";
                                        }
                                        break;
                                    case "2":
                                        palabras = palabras + "Doscientos ";
                                        break;
                                    case "3":
                                        palabras = palabras + "Trescientos ";
                                        break;
                                    case "4":
                                        palabras = palabras + "Cuatrocientos ";
                                        break;
                                    case "5":
                                        palabras = palabras + "Quinientos ";
                                        break;
                                    case "6":
                                        palabras = palabras + "Seiscientos ";
                                        break;
                                    case "7":
                                        palabras = palabras + "Setecientos ";
                                        break;
                                    case "8":
                                        palabras = palabras + "Ochocientos ";
                                        break;
                                    case "9":
                                        palabras = palabras + "Novecientos ";
                                        break;
                                }
                                break;
                            case 2:
                            case 5:
                            case 8:
                                //*********Asigna las palabras para las decenas************
                                switch (Mid(entero, num, 1))
                                {
                                    case "1":
                                        if (Mid(entero, num + 1, 1) == "0")
                                        {
                                            flag = "S";
                                            palabras = palabras + "Diez ";
                                        }
                                        if (Mid(entero, num + 1, 1) == "1")
                                        {
                                            flag = "S";
                                            palabras = palabras + "Once ";
                                        }
                                        if (Mid(entero, num + 1, 1) == "2")
                                        {
                                            flag = "S";
                                            palabras = palabras + "Doce ";
                                        }
                                        if (Mid(entero, num + 1, 1) == "3")
                                        {
                                            flag = "S";
                                            palabras = palabras + "Trece ";
                                        }
                                        if (Mid(entero, num + 1, 1) == "4")
                                        {
                                            flag = "S";
                                            palabras = palabras + "Catorce ";
                                        }
                                        if (Mid(entero, num + 1, 1) == "5")
                                        {
                                            flag = "S";
                                            palabras = palabras + "Quince ";
                                        }
                                        if (Convert.ToInt32(Mid(entero, num + 1, 1)) > 5)
                                        {
                                            flag = "N";
                                            palabras = palabras + "Dieci";
                                        }
                                        break;
                                    case "2":
                                        if (Mid(entero, num + 1, 1) == "0")
                                        {
                                            palabras = palabras + "Veinte ";
                                            flag = "S";
                                        }
                                        else
                                        {
                                            palabras = palabras + "Veinti";
                                            flag = "N";
                                        }
                                        break;
                                    case "3":
                                        if (Mid(entero, num + 1, 1) == "0")
                                        {
                                            palabras = palabras + "Treinta ";
                                            flag = "S";
                                        }
                                        else
                                        {
                                            palabras = palabras + "Treinta y ";
                                            flag = "N";
                                        }
                                        break;
                                    case "4":
                                        if (Mid(entero, num + 1, 1) == "0")
                                        {
                                            palabras = palabras + "Cuarenta ";
                                            flag = "S";
                                        }
                                        else
                                        {
                                            palabras = palabras + "Cuarenta y ";
                                            flag = "N";
                                        }
                                        break;
                                    case "5":
                                        if (Mid(entero, num + 1, 1) == "0")
                                        {
                                            palabras = palabras + "Cincuenta ";
                                            flag = "S";
                                        }
                                        else
                                        {
                                            palabras = palabras + "Cincuenta y ";
                                            flag = "N";
                                        }
                                        break;
                                    case "6":
                                        if (Mid(entero, num + 1, 1) == "0")
                                        {
                                            palabras = palabras + "Sesenta ";
                                            flag = "S";
                                        }
                                        else
                                        {
                                            palabras = palabras + "Sesenta y ";
                                            flag = "N";
                                        }
                                        break;
                                    case "7":
                                        if (Mid(entero, num + 1, 1) == "0")
                                        {
                                            palabras = palabras + "Setenta ";
                                            flag = "S";
                                        }
                                        else
                                        {
                                            palabras = palabras + "Setenta y ";
                                            flag = "N";
                                        }
                                        break;
                                    case "8":
                                        if (Mid(entero, num + 1, 1) == "0")
                                        {
                                            palabras = palabras + "Ochenta ";
                                            flag = "S";
                                        }
                                        else
                                        {
                                            palabras = palabras + "Ochenta y ";
                                            flag = "N";
                                        }
                                        break;
                                    case "9":
                                        if (Mid(entero, num + 1, 1) == "0")
                                        {
                                            palabras = palabras + "Noventa ";
                                            flag = "S";
                                        }
                                        else
                                        {
                                            palabras = palabras + "Noventa y ";
                                            flag = "N";
                                        }
                                        break;
                                }
                                break;
                            case 1:
                            case 4:
                            case 7:
                                //*********Asigna las palabras para las unidades*********
                                switch (Mid(entero, num, 1))
                                {
                                    case "1":
                                        if (flag == "N")
                                        {
                                            if (y == 1)
                                            {
                                                palabras = palabras + "Uno ";
                                            }
                                            else
                                            {
                                                palabras = palabras + "Un ";
                                            }
                                        }
                                        break;
                                    case "2":
                                        if (flag == "N")
                                            palabras = palabras + "Dos ";
                                        break;
                                    case "3":
                                        if (flag == "N")
                                            palabras = palabras + "Tres ";
                                        break;
                                    case "4":
                                        if (flag == "N")
                                            palabras = palabras + "Cuatro ";
                                        break;
                                    case "5":
                                        if (flag == "N")
                                            palabras = palabras + "Cinco ";
                                        break;
                                    case "6":
                                        if (flag == "N")
                                            palabras = palabras + "Seis ";
                                        break;
                                    case "7":
                                        if (flag == "N")
                                            palabras = palabras + "Siete ";
                                        break;
                                    case "8":
                                        if (flag == "N")
                                            palabras = palabras + "Ocho ";
                                        break;
                                    case "9":
                                        if (flag == "N")
                                            palabras = palabras + "Nueve ";
                                        break;
                                }
                                break;
                        }

                        //***********Asigna la palabra mil***************
                        if (y == 4)
                        {
                            if (Mid(entero, 6, 1) != "0" | Mid(entero, 5, 1) != "0" | Mid(entero, 4, 1) != "0" | (Mid(entero, 6, 1) == "0" & Mid(entero, 5, 1) == "0" & Mid(entero, 4, 1) == "0" & entero.Length <= 6))
                                palabras = palabras + "Mil ";
                        }

                        //**********Asigna la palabra millón*************
                        if (y == 7)
                        {
                            if (entero.Length == 7 && Mid(entero, 1, 1) == "1")
                            {
                                palabras = palabras + "Millón ";
                            }
                            else
                            {
                                palabras = palabras + "Millones ";
                            }
                        }
                    }

                    //**********Une la parte entera y la parte decimal*************
                    if (!string.IsNullOrEmpty(dec))
                    {
                        functionReturnValue = palabras + "con " + dec + "/100";
                    }
                    else
                    {
                        functionReturnValue = palabras;
                    }
                }
                else
                {
                    functionReturnValue = "";
                }
            }
            catch (Exception ex)
            {
                //Interaction.MsgBox(ex.Message, MsgBoxStyle.Critical);
            }
            return functionReturnValue;

        }

        public static string Mid(string param, int startIndex, int length)
        {
            string result = param.Substring(startIndex, length);
            return result;
        }

    }
}
